﻿using System;
using System.Configuration;
using System.Data;
using smp.QueryDb.Executing;
using smp.QueryDb.Structure;

namespace smp.QueryDb
{
    internal class DbSession : IDbSession
    {
        #region members and constructors

        private IDbAdapter _adapter;
        private string _connectionString;
        private ILog _log;
        private IDbConnection _connection;
        private IDbTransaction _transaction;
        private bool _openTransaction;

        internal DbSession(ConnectionStringSettings connectionStringSettings, IDbAdapter adapter, ILog log)
        {
            _log = log;
            if (connectionStringSettings == null)
            {
                throw Helper.Error("Invalid or null connection string settings");
            }

            InstanceFactory.RegisterAdapter(adapter, connectionStringSettings.ProviderName);
            _connectionString = connectionStringSettings.ConnectionString;
            _adapter = adapter;
            _connection = _adapter.CreateConnection(_connectionString);
            _log.Debug("Opening connection to : {0}", _connectionString);
            _connection.Open();
        }

        #endregion

        #region Implementation of IDbSession

        public IRawResult Query(Query query)
        {
            var result = new RawResult(query.Structure.RawColumns);
            query.Structure.Finalise(_adapter, true);
            using(var command = query.Structure.GenerateCommand(_connection, _adapter))
            {
                if(_openTransaction)
                {
                    command.Transaction = _transaction;
                }
                using(var reader = command.ExecuteReader())
                {
                    while(reader.Read())
                    {
                        result.Add(reader);
                    }
                }
            }
            if(!query.Structure.HasPaging)
            {
                result.TotalRecords = result.Count;
                return result;
            }

            using(var command = query.Structure.GenerateCountCommand(_connection))
            {
                if (_openTransaction)
                {
                    command.Transaction = _transaction;
                }
                var countResult = command.ExecuteScalar();
                int count;
                if (!int.TryParse(countResult.ToString(), out count))
                {
                    throw Helper.Error("Unable to get total records for query.");
                }
                result.TotalRecords = count;
            }

            return result;
        }

        public IRawResultItem Load(Query query)
        {
            query.Structure.Finalise(_adapter, true);
            IRawResultItem result = null;
            using (var command = query.Structure.GenerateTopCommand(_connection, _adapter, 1))
            {
                if (_openTransaction)
                {
                    command.Transaction = _transaction;
                }
                using(var reader = command.ExecuteReader())
                {
                    while(reader.Read())
                    {
                        result = new RawResultItem(reader, query.Structure.RawColumns);
                        break;
                    }
                }
            }
            return result;
        }

        public IRawResultItem Load(Table table, object id)
        {
            var query = new Query()
                .From(table)
                .Where(new Filter(table.Id).Is.EqualTo(id))
                .Select(table.Id);
            foreach(var column in table.Columns)
            {
                query = query.Select(column);
            }

            return Load(query);
        }

        public IPagedResult<TViewModel> Query<TViewModel>(Query<TViewModel> query) where TViewModel : class 
        {
            var result = new PagedResult<TViewModel>();
            query.Structure.Finalise(_adapter, false);
            using(var command = query.Structure.GenerateCommand(_connection, _adapter))
            {
                if (_openTransaction)
                {
                    command.Transaction = _transaction;
                }
                using(var reader = command.ExecuteReader())
                {
                    while(reader.Read())
                    {
                        var model = query.Structure.CreateModel(typeof (TViewModel), reader);
                        if(model != null)
                        {
                            result.Add(model);
                        }
                    }
                }
            }

            if (!query.Structure.HasPaging)
            {
                result.TotalRecords = result.Results.Count;
                return result;
            }

            using (var command = query.Structure.GenerateCountCommand(_connection))
            {
                if (_openTransaction)
                {
                    command.Transaction = _transaction;
                }
                var countResult = command.ExecuteScalar();
                int count;
                if (!int.TryParse(countResult.ToString(), out count))
                {
                    throw Helper.Error("Unable to get total records for query.");
                }
                result.TotalRecords = count;
            }

            return result;
        }

        public TViewModel Load<TViewModel>(Query<TViewModel> query) where TViewModel : class 
        {
            query.Structure.Finalise(_adapter, false);
            TViewModel model = null;
            using(var command = query.Structure.GenerateTopCommand(_connection, _adapter, 1))
            {
                if (_openTransaction)
                {
                    command.Transaction = _transaction;
                }
                using(var reader = command.ExecuteReader())
                {
                    while(reader.Read())
                    {
                        model = (TViewModel)query.Structure.CreateModel(typeof(TViewModel), reader);
                        break;
                    }
                }
            }
            return model;
        }

        public void StartTransaction()
        {
            StartTransaction(IsolationLevel.ReadCommitted);
        }

        public void StartTransaction(IsolationLevel isolationLevel)
        {
            if (_openTransaction)
                throw Helper.Error("Cannot begin a second transaction on a single session, terminate the first transaction before trying to start a new one.");

            _transaction = _connection.BeginTransaction(isolationLevel);
            _openTransaction = true;
        }

        public void CommitTransaction()
        {
            if (!_openTransaction)
                throw Helper.Error("Cannot commit when no transaction has been initialised.");

            _transaction.Commit();
            _openTransaction = false;
        }

        public void RollbackTransaction()
        {
            if (!_openTransaction)
                throw Helper.Error("Cannot rollback when no transaction has been initialised");

            _transaction.Rollback();
            _openTransaction = false;
        }

        public int Insert(Insert insert)
        {
            using (var command = Helper.CreateAdoCommand(_connection, insert.GetCommand(_adapter), CommandType.Text, _transaction))
            {
                return command.ExecuteNonQuery();
            }
        }

        public object InsertReturningId(Insert insert)
        {
            //TODO NOT SURE I LIKE THIS ANYWAY
            throw new NotImplementedException();
        }

        public int Update(Update update)
        {
            using (var command = Helper.CreateAdoCommand(_connection, update.GetCommand(_adapter), CommandType.Text, _transaction))
            {
                return command.ExecuteNonQuery();
            }
        }

        public int Delete(Delete delete)
        {
            using (var command = Helper.CreateAdoCommand(_connection, delete.GetCommand(_adapter), CommandType.Text, _transaction))
            {
                return command.ExecuteNonQuery();
            }
        }

        public void Execute(Execute execute)
        {
            using (var command = Helper.CreateAdoCommand(_connection, execute.Command, CommandType.StoredProcedure, _transaction))
            {
                command.ExecuteNonQuery();
            }
        }

        public IRawResult ExecuteRead(ExecuteRead executeRead)
        {
            var result = new RawResult(executeRead.Fields);
            using (var command = Helper.CreateAdoCommand(_connection, executeRead.Command, CommandType.StoredProcedure, _transaction))
            {
                using(var reader = command.ExecuteReader())
                {
                    while(reader.Read())
                    {
                        result.Add(reader);
                    }
                }
            }
            result.TotalRecords = result.Count;
            return result;
        }

        public IPagedResult<TViewModel> ExecuteRead<TViewModel>(ExecuteRead<TViewModel> executeRead)
        {
            var result = new PagedResult<TViewModel>();
            using (var command = Helper.CreateAdoCommand(_connection, executeRead.Command, CommandType.StoredProcedure, _transaction))
            {
                using (var reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        var model = executeRead.CreateObject(typeof (TViewModel), reader);
                        if (model != null)
                        {
                            result.Add(model);
                        }
                    }
                }
            }
            result.TotalRecords = result.Results.Count;
            return result;
        }

        #endregion

        #region Implementation of IDisposable

        public void Dispose()
        {
            if (_openTransaction && _transaction != null)
            {
                _transaction.Rollback();
                _openTransaction = false;
                _transaction.Dispose();
            }
            if (_connection != null)
            {
                if (_connection.State == ConnectionState.Connecting)
                {
                    while (_connection.State == ConnectionState.Connecting) { }
                }
                if (_connection.State == ConnectionState.Open)
                {
                    _connection.Close();
                }
                _connection.Dispose();
            }
        }

        #endregion
    }
}
